import pandas as pd
import numpy as np

# 读取 Excel 文件
traffic_df = pd.read_excel("MDOT_SHA_Annual_Average_Daily_Traffic_Baltimore.xlsx", engine="openpyxl")
edges_df = pd.read_excel("edges_all.xlsx", engine="openpyxl")
edges_df["f"] = 0

def trans_string(s):
    s = s.replace('{', '[').replace('}', ']')
    return s

station_dic = {}
edges_dic = {}
edgea_f = {}
for index, traffic_row in traffic_df.iterrows():
    print("流量表遍历到第", index, "  行")
    if not pd.isnull(traffic_row['node start']):
        traffic_start = eval(trans_string(str(traffic_row['node start'])))
        if len(traffic_start):
            for start_node in traffic_start:
                if str(start_node) not in station_dic.keys():
                    station_dic[str(start_node)] = []
                station_dic[str(start_node)].append(traffic_row)
    if not pd.isnull(traffic_row['node(s) end']):
        traffic_end = eval(trans_string(str(traffic_row['node(s) end'])))
        if len(traffic_end):
            for end_node in traffic_end:
                if str(end_node) not in station_dic.keys():
                    station_dic[str(end_node)] = []
                station_dic[str(end_node)].append(traffic_row)


i = 0
for index, edge_row in edges_df.iterrows():
    print('edge遍历到了', i)
    edge_u = edge_row["u"]
    edge_v = edge_row["v"]
    if str(edge_u) in station_dic.keys() :
        for traffic_node in station_dic[str(edge_u)]:
            if traffic_node["Station ID"] not in edges_dic.keys() :
                edges_dic [traffic_node["Station ID"]] = (traffic_node, [])
            edges_dic[traffic_node["Station ID"]][1].append(i)
    if str(edge_v) in station_dic.keys():
        for traffic_node in station_dic[str(edge_v)]:
            if traffic_node["Station ID"] not in edges_dic.keys():
                edges_dic[traffic_node["Station ID"]] = (traffic_node, [])
            edges_dic[traffic_node["Station ID"]][1].append(i)
    i += 1

print('流量表中共有', len(edges_dic), '行找到了关联的边')

idx = 0
for stationID, assosiatedData in edges_dic.items():
    traffic_row, edge_rows = assosiatedData
    traffic_f = ( traffic_row['AADT 2019'] + traffic_row['AADT 2020'] + traffic_row['AADT 2021'] + traffic_row['AADT 2022']) / 4
    length = len(edge_rows)
    if length > 0 :
        traffic_f = traffic_f / len(edge_rows)
    print(traffic_f)
    for edge_row in edge_rows:
        edge_fnow = edges_df.at[edge_row,"f"] + traffic_f
        edges_df.loc[edge_row, "f"] = edge_fnow
        print(edges_df.at[edge_row,"f"])
    print("edge_dic 遍历到" , idx + 1 ,"行")
    idx += 1

edges_df.to_excel('edges_output.xlsx', index=False)
print("文件已保存")